-- =============================================================================
-- Diagram Name: Dotazniky
-- Created on: 1.3.2008 13:56:27
-- Diagram Version: 10
-- =============================================================================
CREATE DOMAIN "tag" AS varchar(255)
	NOT NULL;

SET CHECK_FUNCTION_BODIES TO FALSE;

CREATE OR REPLACE FUNCTION "lo_update" () 	
RETURNS trigger AS
$BODY$
	DECLARE
	foo integer;
	BEGIN
	IF NEW.body != OLD.body AND OLD.body != null THEN
	SELECT LO_UNLINK(old.body) INTO foo;
	END IF;
	RETURN NEW;
	END;
$BODY$
	LANGUAGE plpgsql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;


CREATE OR REPLACE FUNCTION "change_order_after_delete" (IN tabulka text, IN attrnames text[], IN attrvalues int4[], IN ordering_old int4) 	
RETURNS void AS
$BODY$
	DECLARE
	t TEXT := '';
	BEGIN
	t := 'UPDATE '
	|| tabulka
	|| ' SET
	ordering = ordering - 1
	WHERE
	ordering > ' || ordering_old;
	IF size(attrNames) > 0 THEN
	t := t || ' AND ' || keys(attrNames, attrValues, ' AND '::text);
	END IF;
	
	EXECUTE t;
	END;
$BODY$
	LANGUAGE plpgsql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;


CREATE OR REPLACE FUNCTION "change_order_after_insert" (IN tabulka text, IN attrnames text[], IN attrvalues int4[], IN ordering_new int4) 	
RETURNS void AS
$BODY$
	DECLARE
	t TEXT := '';
	BEGIN
	t := 'UPDATE '
	|| tabulka
	|| ' SET
	ordering = ordering + 1
	WHERE
	ordering >= '
	|| ordering_new;
	IF size(AttrNames) > 0 THEN
	t := t || ' AND ' || keys(attrNames, attrValues, ' AND '::text);
	END IF;
	EXECUTE t;
	END;
$BODY$
	LANGUAGE plpgsql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION "change_order_after_update" (IN tabulka text, IN attrnames text[], IN attrvalues int4[], IN ordering_new int4, IN ordering_old int4) 	
RETURNS void AS
$BODY$
	DECLARE
	t TEXT := '';
	BEGIN
	IF ordering_new < ordering_old THEN
	t := 'UPDATE '
	|| tabulka
	|| ' SET ordering = ordering + 1
	WHERE ordering >= '
	|| ordering_new
	|| ' AND ordering <= '
	|| ordering_old;
	IF size(AttrNames) > 0 THEN
	t := t || ' AND ' || keys(attrNames, attrValues, ' AND '::text);
	END IF;
	ELSEIF ordering_new > ordering_old THEN
	t := 'UPDATE '
	|| tabulka
	|| ' SET ordering = ordering - 1
	WHERE ordering <= '
	|| ordering_new
	|| ' AND ordering >= '
	|| ordering_old;
	IF size(AttrNames) > 0 THEN
	t := t || ' AND ' || keys(attrNames, attrValues, ' AND '::text);
	END IF;
	END IF;
	EXECUTE t;
	END;
$BODY$
	LANGUAGE plpgsql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION "keys" (IN names text[], IN values int4[], IN delimeter text) 	
RETURNS text AS
$BODY$
	DECLARE
	t TEXT := '';
	tmp TEXT := '';
	i INTEGER := 1;
	BEGIN
	
	IF size(names) != size(values) THEN
	RAISE EXCEPTION 'Input arrays have different size';
	END IF;
	
	WHILE i <= size($1) LOOP
	tmp := '';
	tmp := names[i] || ' = ' || values[i];
	IF i > 1 THEN
	t := t || delimeter;
	END IF;
	t := t || tmp;
	i := i + 1;
	END LOOP;
	
	RETURN t;
	END;
$BODY$
	LANGUAGE plpgsql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;


CREATE OR REPLACE FUNCTION "responses_save_response" (IN id_survey_run int4, IN id_page_question int4, IN int_value int4, IN text_value int4) 	
RETURNS int8 AS
$BODY$
	INSERT INTO responses(id_survey_run, id_page_question, int_value, text_value)
	VALUES($1, $2, $3, $4);
	
	SELECT CURRVAL('responses_id_seq'::regclass);
$BODY$
	LANGUAGE sql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;


CREATE OR REPLACE FUNCTION "responses_save_run" (IN in_invitation int4) 	
RETURNS int8 AS
$BODY$
	INSERT INTO runs(id_invitation, timestamp)
	VALUES ($1, now());
	
	SELECT CURRVAL('runs_id_seq'::regclass);
$BODY$
	LANGUAGE sql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION "responses_save_survey_run" (IN in_campaign_survey int4, IN in_run int4, IN in_object int4) 	
RETURNS int8 AS
$BODY$
	INSERT INTO survey_runs(id_campaign_survey, id_run, id_object)
	VALUES($1, $2, $3);
	
	SELECT CURRVAL('survey_runs_id_seq'::regclass);
$BODY$
	LANGUAGE sql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;


CREATE OR REPLACE FUNCTION "size" (IN  text[]) 	
RETURNS int4 AS
$BODY$
	DECLARE
	data_array ALIAS FOR $1;
	array_element text;
	counter int4;
	BEGIN
	-- set the counter
	counter := 0;
	
	-- loop until we terminate
	WHILE true LOOP
	-- get the element from the array
	array_element := data_array[counter + 1];  -- 1 based arrays
	
	-- exit the loop if no more data
	IF (array_element IS NULL) THEN
	EXIT;   -- exit the  loop
	END IF;
	
	
	-- increment the counter
	counter := counter + 1;
	END LOOP;
	RETURN counter;
	END;
$BODY$
	LANGUAGE plpgsql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;


CREATE OR REPLACE FUNCTION "size" (IN  int4[]) 	
RETURNS int4 AS
$BODY$
	DECLARE
	data_array ALIAS FOR $1;
	array_element int4;
	counter int4;
	BEGIN
	-- set the counter
	counter := 0;
	
	-- loop until we terminate
	WHILE true LOOP
	-- get the element from the array
	array_element := data_array[counter + 1];  -- 1 based arrays
	
	-- exit the loop if no more data
	IF (array_element IS NULL) THEN
	EXIT;   -- exit the  loop
	END IF;
	
	
	-- increment the counter
	counter := counter + 1;
	END LOOP;
	RETURN counter;
	END;
$BODY$
	LANGUAGE plpgsql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;


CREATE OR REPLACE FUNCTION "translate" (IN id_string int4, IN id_lang int4) 	
RETURNS varchar AS
$BODY$
	SELECT text FROM strings WHERE id_string = $1 AND id_lang = $2
$BODY$
	LANGUAGE SQL
	CALLED ON NULL INPUT
	IMMUTABLE
	EXTERNAL SECURITY INVOKER;


SET CHECK_FUNCTION_BODIES TO TRUE;


CREATE TABLE "public"."scheduled_tasks" (
  "name" varchar(64) NOT NULL,
  "last_run" timestamp with time zone,
  "is_running" bool NOT NULL DEFAULT False,
  CONSTRAINT "scheduled_tasks_pkey" PRIMARY KEY("name")
)
WITHOUT OIDS;


CREATE TABLE "public"."runs" (
  "id" SERIAL NOT NULL,
  "id_invitation" int4,
  "timestamp" timestamp with time zone NOT NULL DEFAULT NOW(),
  CONSTRAINT "runs_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."runs" IS 'urcuje unikatnost hlasujuceho
ten hlasoval v urcitu dobu
a mohol dostat pozvanku';

CREATE TABLE "public"."string_ids" (
  "id" SERIAL NOT NULL,
  "id_user" int4,
  "group" int2 NOT NULL,
  CONSTRAINT "string_ids_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."string_ids" IS 'tabulka sluzi na prepojenie so skutocnymi prekladmi stringov';
COMMENT ON COLUMN "public"."string_ids"."group" IS 'for difference between types of strings (question, notice, ...)';

CREATE TABLE "public"."sources" (
  "id" SERIAL NOT NULL,
  "name" varchar NOT NULL,
  "id_user" int4,
  CONSTRAINT "sources_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;


CREATE TABLE "public"."responses" (
  "id" SERIAL NOT NULL,
  "id_survey_run" int4,
  "id_page_question" int4,
  "int_value" int4,
  "text_value" varchar,
  CONSTRAINT "responses_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."responses" IS 'samotne odpovede
obsahuje:
run do ktoreho patri
otazku, na ktoru sa odpoveda
hodnotu otazky';

CREATE TABLE "public"."property_tags" (
  "id_property" int4 NOT NULL,
  "tag" "tag" NOT NULL,
  CONSTRAINT "property_tags_pkey" PRIMARY KEY("id_property","tag")
)
WITHOUT OIDS;

CREATE INDEX "id_property_idx" ON "public"."property_tags" ("id_property");
CREATE INDEX "property_tag_idx" ON "public"."property_tags" ("tag");

CREATE TABLE "public"."properties" (
  "id" SERIAL NOT NULL,
  "name" int4 NOT NULL,
  "id_user" int4 NOT NULL,
  "shared" bool NOT NULL,
  "type" int2 NOT NULL,
  CONSTRAINT "properties_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."properties" IS 'property, ktore sa mozu nastavovat objektom';

CREATE TABLE "public"."questions" (
  "id" SERIAL NOT NULL,
  "id_user" int4,
  "text" int4 NOT NULL,
  "type" int2 NOT NULL,
  "shared" bool NOT NULL,
  CONSTRAINT "questions_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."questions" IS 'vsetky otazky';
COMMENT ON COLUMN "public"."questions"."id_user" IS 'moze byt null, pretoze otazka moze byt zdielana';
COMMENT ON COLUMN "public"."questions"."type" IS 'type of question

0) volba medzi textami
1) volba medzi textami s moznostou dopisania textu
2) volba medzi cislami
3) volba medzi cislami s moznostou dopisania textu
4) volba vyzadujuca ako vstup vacsie textove pole
5) volba vyzadujuca ako vstup male textove pole
6)  volba vyzaduje ciselny vstup';
COMMENT ON COLUMN "public"."questions"."shared" IS 'bude ju moct pouzivat aj niekto dalsi?';

CREATE TABLE "public"."question_tags" (
  "id_question" int4 NOT NULL,
  "tag" "tag" NOT NULL,
  CONSTRAINT "question_tags_pkey" PRIMARY KEY("id_question","tag")
)
WITHOUT OIDS;

CREATE INDEX "id_question_idx" ON "public"."question_tags" ("id_question");
CREATE INDEX "question_tag_idx" ON "public"."question_tags" ("tag");
COMMENT ON TABLE "public"."question_tags" IS 'question tags';

CREATE TABLE "public"."strings" (
  "id_string" int4 NOT NULL,
  "id_lang" int4 NOT NULL,
  "text" text NOT NULL,
  CONSTRAINT "strings_pkey" PRIMARY KEY("id_string","id_lang")
)
WITHOUT OIDS;

CREATE INDEX "id_string_idx" ON "public"."strings" ("id_string");
COMMENT ON TABLE "public"."strings" IS '- skutocne texty jednotlivych poloziek - kvoli jazykom
-primarny kluc je s id_lang, pretoze je potrebne, aby sa stringy dali nejako groupovat (pre preklad do inych jazykov)';

CREATE TABLE "public"."user_groups" (
  "id" SERIAL NOT NULL,
  "name" int4,
  "max_added_questions" int4,
  "max_added_objects" int4,
  "max_questions" int4,
  "max_objects" int4,
  "max_results" int4,
  "single_user" bool NOT NULL DEFAULT false,
  "can_share_objects" bool NOT NULL DEFAULT false,
  "can_share_questions" bool NOT NULL DEFAULT false,
  "max_added_properties" int4,
  "can_share_properties" bool NOT NULL DEFAULT false,
  "any_tags" bool NOT NULL DEFAULT false,
  CONSTRAINT "user_groups_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."user_groups" IS 'opravnenia a nastavenia pre jednotlive groupy (alebo individualneho usera, ak nepozuiva defaultne...)
permissions and settings for groups (and individial users if not using default)';
COMMENT ON COLUMN "public"."user_groups"."any_tags" IS 'determines if user is able to set any tag for any object or if he/she is limited to tags speciefied in group_tags';

CREATE TABLE "public"."templates" (
  "id" SERIAL NOT NULL,
  "id_user" int4,
  "name" varchar(32) NOT NULL,
  "body" oid NOT NULL,
  CONSTRAINT "templates_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;


CREATE OR REPLACE RULE "lo_templates_delete" AS
	ON DELETE TO "public"."templates"
	DO ALSO
		(SELECT lo_unlink(old.body) AS lo_unlink);
COMMENT ON COLUMN "public"."templates"."body" IS 'telo templatu';

CREATE TABLE "public"."users" (
  "id" SERIAL NOT NULL,
  "id_group" int4 NOT NULL,
  "id_group_self" int4,
  "id_template_set" int4,
  "login" varchar(32) NOT NULL,
  "password" varchar(32) NOT NULL,
  "id_lang" int4 NOT NULL,
  "email" varchar(256) NOT NULL,
  "is_admin" bool NOT NULL DEFAULT false,
  "realname" varchar(64) NOT NULL DEFAULT ' '::character varying,
  CONSTRAINT "users_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

CREATE UNIQUE INDEX "login_idx" ON "public"."users" ("login");
COMMENT ON TABLE "public"."users" IS 'tabulka uzivatelov';
COMMENT ON COLUMN "public"."users"."id_group_self" IS 'ak uzivatel potrebuje specialne nastavenia, vytvori sa mu osobita skupina a v nej ma nastavenia ulozene ';

CREATE TABLE "public"."user_strings" (
  "id" SERIAL NOT NULL,
  "id_user" int4 NOT NULL,
  "string" int4 NOT NULL,
  "name" varchar(32) NOT NULL,
  CONSTRAINT "user_strings_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."user_strings" IS 'uzivatelske stringy, ktore si moze uzivatel definovat ako vlastny string';

CREATE TABLE "public"."template_sets" (
  "id" SERIAL NOT NULL,
  "header" int4,
  "footer" int4,
  "first_page" int4,
  "last_page" int4,
  "bad_object" int4,
  "no_invite" int4,
  "completed" int4,
  "id_user" int4,
  CONSTRAINT "template_sets_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."template_sets" IS 'templaty pre rozne stavy';
COMMENT ON COLUMN "public"."template_sets"."id_user" IS 'vytvoril user';

CREATE TABLE "public"."survey_runs" (
  "id" SERIAL NOT NULL,
  "id_campaign_survey" int4,
  "id_run" int4,
  "id_object" int4,
  CONSTRAINT "survey_runs_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."survey_runs" IS 'tabulka urcuje na aky objekt sa odpovedalo, na aky dotaznik sa odpovedalo a "ktoremu uzivatelovi patri" - z tabulky runs';

CREATE TABLE "public"."survey_pages" (
  "id" SERIAL NOT NULL,
  "id_survey" int4 NOT NULL,
  "title" int4,
  "ordering" int4 NOT NULL,
  CONSTRAINT "survey_pages_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."survey_pages" IS 'jednotlive stranky surveyu - cely survey sa nezobrazuje ako celok, ale ako postupnost stranok';
COMMENT ON COLUMN "public"."survey_pages"."title" IS 'titulok stranky surveya';
COMMENT ON COLUMN "public"."survey_pages"."ordering" IS 'poradie stranky v surveyi';

CREATE TABLE "public"."surveys" (
  "id" SERIAL NOT NULL,
  "id_user" int4 NOT NULL,
  "id_template_set" int4,
  "name" int4 NOT NULL,
  CONSTRAINT "surveys_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."surveys" IS 'samotny survey, ma nazov,  template set, podla ktoreho sa sprava';
COMMENT ON COLUMN "public"."surveys"."id_template_set" IS 'templateset, ktory sa bude pouzivat pre dany survey';
COMMENT ON COLUMN "public"."surveys"."name" IS 'nazov dotaznika';

CREATE TABLE "public"."survey_tags" (
  "id_survey" int4 NOT NULL,
  "tag" "tag" NOT NULL,
  CONSTRAINT "survey_tags_pkey" PRIMARY KEY("id_survey","tag")
)
WITHOUT OIDS;

CREATE INDEX "id_survey_idx" ON "public"."survey_tags" ("id_survey");
CREATE INDEX "survey_tag_idx" ON "public"."survey_tags" ("tag");
COMMENT ON TABLE "public"."survey_tags" IS 'tagy na jednotlive surveye
';

CREATE TABLE "public"."group_tags" (
  "id_group" int4 NOT NULL,
  "tag" "tag" NOT NULL,
  CONSTRAINT "group_tags_pkey" PRIMARY KEY("id_group","tag")
)
WITHOUT OIDS;

CREATE INDEX "id_group_idx" ON "public"."group_tags" ("id_group");
CREATE INDEX "group_tag_idx" ON "public"."group_tags" ("tag");
COMMENT ON TABLE "public"."group_tags" IS 'list of tags accesable by groups';

CREATE TABLE "public"."fields" (
  "id" SERIAL NOT NULL,
  "id_user" int4 NOT NULL,
  "name" varchar(32) NOT NULL,
  "required" bool NOT NULL,
  CONSTRAINT "fields_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."fields" IS 'extrafields for invitations';

CREATE TABLE "public"."import_tags" (
  "id_import" SERIAL NOT NULL,
  "tag" varchar(255) NOT NULL,
  CONSTRAINT "import_tags_pkey" PRIMARY KEY("id_import","tag")
)
WITHOUT OIDS;

CREATE INDEX "id_import_idx" ON "public"."import_tags" ("id_import");
CREATE INDEX "import_tag_idx" ON "public"."import_tags" ("tag");
COMMENT ON TABLE "public"."import_tags" IS 'tagy pridavane jednotlivym polozkam v importe';

CREATE TABLE "public"."import_settings" (
  "id" SERIAL NOT NULL,
  "id_user" int4,
  "name" varchar NOT NULL,
  "url" varchar NOT NULL,
  "cron" bool NOT NULL DEFAULT False,
  CONSTRAINT "import_settings_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."import_settings" IS 'konfiguracna tabulka pre import XML do databazy';
COMMENT ON COLUMN "public"."import_settings"."id_user" IS 'vytvoril uzivatel';
COMMENT ON COLUMN "public"."import_settings"."url" IS 'url suboru, ktory sa ma importovat';
COMMENT ON COLUMN "public"."import_settings"."cron" IS 'ci je import vlozeny do cronu';

CREATE TABLE "public"."evaluation" (
  "id" SERIAL NOT NULL,
  "name" int4 NOT NULL,
  "id_parent" int4,
  PRIMARY KEY("id")
)
WITHOUT OIDS;


CREATE TABLE "public"."campaigns" (
  "id" SERIAL NOT NULL,
  "id_user" int4 NOT NULL,
  "name" int4 NOT NULL,
  "id_template_set" int4,
  "id_email_template" int4,
  "active" bool NOT NULL DEFAULT False,
  "public" bool NOT NULL DEFAULT true,
  "valid_from" timestamp with time zone,
  "valid_to" timestamp with time zone,
  CONSTRAINT "campaigns_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."campaigns" IS 'kampan je suhrn surveyov (aj) o roznych objektoch (napr. operatorske pracovisko, cesta, hotel, ine sluzby atd.)';
COMMENT ON COLUMN "public"."campaigns"."active" IS 'indicates if campaign is active or not';
COMMENT ON COLUMN "public"."campaigns"."public" IS 'indicates if campaign is public or not (can be accessed by URL or just by invitation)';
COMMENT ON COLUMN "public"."campaigns"."valid_from" IS 'the day since which the campaign is active';
COMMENT ON COLUMN "public"."campaigns"."valid_to" IS 'the day till which the campaign is active';

CREATE TABLE "public"."campaign_surveys" (
  "id" SERIAL NOT NULL,
  "id_campaign" int4 NOT NULL,
  "id_survey" int4 NOT NULL,
  "first_page" int4,
  "ordering" int4 NOT NULL,
  CONSTRAINT "campaign_surveys_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."campaign_surveys" IS 'kampan sa sklada z viacerych surveyov 
   &&
survey moze byt vo viacerych kampaniach

obsahuje template prvej stranky

PK je id z dovodu zbytocne komplikovaneho pristupu k prvkom v Zend Frameworku';

CREATE TABLE "public"."email_templates" (
  "id" SERIAL NOT NULL,
  "id_user" int4,
  "name" int4 NOT NULL,
  "bcc" text,
  "is_html" bool NOT NULL DEFAULT False,
  "cc" text,
  "subject" text NOT NULL,
  "to" text NOT NULL,
  "body" text NOT NULL,
  CONSTRAINT "email_templates_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."email_templates" IS 'well ... just email templates :-)';

CREATE TABLE "public"."choices" (
  "id" SERIAL NOT NULL,
  "text" int4 NOT NULL,
  "id_question" int4 NOT NULL,
  "value" int4,
  "ordering" int4 NOT NULL,
  CONSTRAINT "choices_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."choices" IS 'mozne odpovede k danej otazke';
COMMENT ON COLUMN "public"."choices"."value" IS 'ciselna hodnota moznosti';

CREATE TABLE "public"."invitation_fields" (
  "id_invitation" int4 NOT NULL,
  "id_field" int4 NOT NULL,
  "value" text NOT NULL,
  CONSTRAINT "invitation_fields_pkey" PRIMARY KEY("id_invitation","id_field")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."invitation_fields" IS 'values for invitation extra fields';

CREATE TABLE "public"."object_tags" (
  "id_object" int4 NOT NULL,
  "tag" "tag" NOT NULL,
  CONSTRAINT "object_tags_pkey" PRIMARY KEY("id_object","tag")
)
WITHOUT OIDS;

CREATE INDEX "id_object_idx" ON "public"."object_tags" ("id_object");
CREATE INDEX "tag_idx" ON "public"."object_tags" ("tag");
COMMENT ON TABLE "public"."object_tags" IS 'object tags
';

CREATE TABLE "public"."object_properties" (
  "id_property" int4 NOT NULL,
  "id_object" int4 NOT NULL,
  "text_value" int4,
  "int_value" int4,
  "untranslated_value" varchar,
  CONSTRAINT "object_properties_pkey" PRIMARY KEY("id_property","id_object")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."object_properties" IS 'actual values of properties for objects';

CREATE TABLE "public"."page_questions" (
  "id" SERIAL NOT NULL,
  "id_page" int4 NOT NULL,
  "id_question" int4 NOT NULL,
  "id_evaluation" int4,
  "ordering" int4 NOT NULL,
  "description" int4,
  "required" bool NOT NULL DEFAULT false,
  "weight" float4 NOT NULL DEFAULT 1,
  "min" int4 DEFAULT -2,
  "max" int4 DEFAULT 2,
  CONSTRAINT "survey_questions_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."page_questions" IS 'skupiny otazok, ktore su spolocne na jednej stranke';
COMMENT ON COLUMN "public"."page_questions"."id_page" IS 'stranka, na ktorej je skupina otazok';
COMMENT ON COLUMN "public"."page_questions"."ordering" IS 'poradie otazky na stranke';
COMMENT ON COLUMN "public"."page_questions"."description" IS 'popis otazky.. (nejake info, ze co ma napisat - vyjadrite svoj nazor...)';
COMMENT ON COLUMN "public"."page_questions"."required" IS 'ak na nejaku otazku MUSI byt odpoved - inak nepusti na dalsiu stranku napr.';
COMMENT ON COLUMN "public"."page_questions"."weight" IS 'aku vahu ma tato otazka va danej skupine otazok
je pouzita tu, pretoze ta ista otazka moze mat v roznych skupinach inu vahu';
COMMENT ON COLUMN "public"."page_questions"."min" IS 'hodnota, ktore sa zoberie, ak bola zadana hodnota nizsia ako minimum';
COMMENT ON COLUMN "public"."page_questions"."max" IS 'hodnota, ktora sa zoberie, ak bola zadana hodnota vyssia ako maximum';

CREATE TABLE "public"."objects" (
  "id" SERIAL NOT NULL,
  "name" int4 NOT NULL,
  "id_user" int4,
  "id_source" int4,
  "shared" bool NOT NULL,
  "original_id" int4,
  "timestamp" timestamp with time zone,
  CONSTRAINT "objects_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

CREATE INDEX "id_source_idx" ON "public"."objects" ("id_source");
CREATE UNIQUE INDEX "id_source_original_id_idx" ON "public"."objects" ("id_source" , "original_id");
COMMENT ON TABLE "public"."objects" IS 'zoznam hodnotenych objektov';
COMMENT ON COLUMN "public"."objects"."id_user" IS 'moze byt null, pretoze objekt moze byt zdielany';
COMMENT ON COLUMN "public"."objects"."id_source" IS 'id source databazy';
COMMENT ON COLUMN "public"."objects"."original_id" IS 'povodne id v databaze source';
COMMENT ON COLUMN "public"."objects"."timestamp" IS 'timestamp poslednej zmeny v source databaze';

CREATE TABLE "public"."media" (
  "id" SERIAL NOT NULL,
  "id_user" int4 NOT NULL,
  "name" varchar(32) NOT NULL,
  "type" varchar(32) NOT NULL,
  "body" oid NOT NULL,
  CONSTRAINT "media_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;


CREATE TRIGGER "lo_media_update" BEFORE UPDATE 
	ON "public"."media" FOR EACH ROW
	EXECUTE PROCEDURE "lo_update"();


CREATE TRIGGER "lo_templates_update" BEFORE UPDATE 
	ON "public"."media" FOR EACH ROW
	EXECUTE PROCEDURE "lo_update"();


CREATE OR REPLACE RULE "lo_media_delete" AS
	ON DELETE TO "public"."media"
	DO ALSO
		(SELECT lo_unlink(old.body) AS lo_unlink);

CREATE TABLE "public"."invitations" (
  "id" SERIAL NOT NULL,
  "id_campaign" int4 NOT NULL,
  "id_lang" int4 NOT NULL,
  "hash" varchar(64) NOT NULL,
  "valid_until" timestamp with time zone DEFAULT (now() + '14 days'::interval),
  "note" text,
  "email_at" timestamp with time zone,
  "email_sent" bool NOT NULL,
  CONSTRAINT "invitations_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."invitations" IS 'user may be invited somehow to fill the survey';

CREATE TABLE "public"."invitation_objects" (
  "id_invitation" int4 NOT NULL,
  "id_object" int4 NOT NULL,
  "id_campaignsurvey" int4 NOT NULL,
  CONSTRAINT "invitation_objects_pkey" PRIMARY KEY("id_invitation","id_object","id_campaignsurvey")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."invitation_objects" IS 'pozvanka pre jednotlive objekty / kampan';

CREATE TABLE "public"."log" (
  "id" SERIAL NOT NULL,
  "timestamp" timestamp with time zone NOT NULL,
  "priority" int2 NOT NULL,
  "message" text NOT NULL,
  "detail" text,
  CONSTRAINT "log_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;


CREATE TABLE "public"."langs" (
  "id" SERIAL NOT NULL,
  "name" varchar(32) NOT NULL,
  "longname" varchar(32) NOT NULL,
  "altnames" varchar[],
  "similar" int4[],
  CONSTRAINT "langs_pkey" PRIMARY KEY("id")
)
WITHOUT OIDS;

COMMENT ON TABLE "public"."langs" IS 'zoznam moznych jazykov';
COMMENT ON COLUMN "public"."langs"."name" IS 'laguage name as specified in RFC1766';
COMMENT ON COLUMN "public"."langs"."longname" IS 'the whole name of language';


ALTER TABLE "public"."runs" ADD CONSTRAINT "run ma pozvanku" FOREIGN KEY ("id_invitation")
    REFERENCES "public"."invitations"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."string_ids" ADD CONSTRAINT "vytvoril uzivatel" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."sources" ADD CONSTRAINT "vytvoril user" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."responses" ADD CONSTRAINT "odpoved na otazku" FOREIGN KEY ("id_page_question")
    REFERENCES "public"."page_questions"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."responses" ADD CONSTRAINT "odpoved na survey_run" FOREIGN KEY ("id_survey_run")
    REFERENCES "public"."survey_runs"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."property_tags" ADD CONSTRAINT "property ma tagy" FOREIGN KEY ("id_property")
    REFERENCES "public"."properties"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."properties" ADD CONSTRAINT "nazov property v stringu" FOREIGN KEY ("name")
    REFERENCES "public"."string_ids"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."properties" ADD CONSTRAINT "property vytvoril user" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."questions" ADD CONSTRAINT "otazku vytvoril user" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."questions" ADD CONSTRAINT "text otazky v stringu" FOREIGN KEY ("text")
    REFERENCES "public"."string_ids"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."question_tags" ADD CONSTRAINT "tag k otazke" FOREIGN KEY ("id_question")
    REFERENCES "public"."questions"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."strings" ADD CONSTRAINT "preklad daneho stringu" FOREIGN KEY ("id_string")
    REFERENCES "public"."string_ids"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."strings" ADD CONSTRAINT "string je v jazyku" FOREIGN KEY ("id_lang")
    REFERENCES "public"."langs"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."user_groups" ADD CONSTRAINT "nazov skupiny v stringu" FOREIGN KEY ("name")
    REFERENCES "public"."string_ids"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."templates" ADD CONSTRAINT "template vytvoril user" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."users" ADD CONSTRAINT "user patri do skupiny" FOREIGN KEY ("id_group")
    REFERENCES "public"."user_groups"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."users" ADD CONSTRAINT "user pouziva template" FOREIGN KEY ("id_template_set")
    REFERENCES "public"."template_sets"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."users" ADD CONSTRAINT "sam user v groupe" FOREIGN KEY ("id_group_self")
    REFERENCES "public"."user_groups"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."user_strings" ADD CONSTRAINT "stringy patriaca userovi" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."user_strings" ADD CONSTRAINT "userove stringy v stringu" FOREIGN KEY ("string")
    REFERENCES "public"."string_ids"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."template_sets" ADD CONSTRAINT "template pre footer" FOREIGN KEY ("footer")
    REFERENCES "public"."templates"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."template_sets" ADD CONSTRAINT "template pre header" FOREIGN KEY ("header")
    REFERENCES "public"."templates"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."template_sets" ADD CONSTRAINT "template pre poslednu stranku" FOREIGN KEY ("last_page")
    REFERENCES "public"."templates"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."template_sets" ADD CONSTRAINT "template pre pristup bez pozvanky" FOREIGN KEY ("no_invite")
    REFERENCES "public"."templates"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."template_sets" ADD CONSTRAINT "template pre prvu stranku" FOREIGN KEY ("first_page")
    REFERENCES "public"."templates"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."template_sets" ADD CONSTRAINT "template pre stranku po vyplneni vsetkeho" FOREIGN KEY ("completed")
    REFERENCES "public"."templates"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."template_sets" ADD CONSTRAINT "template pre zly objekt" FOREIGN KEY ("bad_object")
    REFERENCES "public"."templates"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."template_sets" ADD CONSTRAINT "template_set vytvoril user" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."survey_runs" ADD CONSTRAINT "dotaznik z kampane" FOREIGN KEY ("id_campaign_survey")
    REFERENCES "public"."campaign_surveys"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."survey_runs" ADD CONSTRAINT "survey run na objekt" FOREIGN KEY ("id_object")
    REFERENCES "public"."objects"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."survey_runs" ADD CONSTRAINT "dotaznik je z runu" FOREIGN KEY ("id_run")
    REFERENCES "public"."runs"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."survey_pages" ADD CONSTRAINT "nazov stranky v stringu" FOREIGN KEY ("title")
    REFERENCES "public"."string_ids"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."survey_pages" ADD CONSTRAINT "stranka patri surveyu" FOREIGN KEY ("id_survey")
    REFERENCES "public"."surveys"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."surveys" ADD CONSTRAINT "dotaznik pouziva template" FOREIGN KEY ("id_template_set")
    REFERENCES "public"."template_sets"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."surveys" ADD CONSTRAINT "dotaznik vytvoril user" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."survey_tags" ADD CONSTRAINT "tagy na dotaznik" FOREIGN KEY ("id_survey")
    REFERENCES "public"."surveys"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."group_tags" ADD CONSTRAINT "skupina ma tagy" FOREIGN KEY ("id_group")
    REFERENCES "public"."user_groups"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."fields" ADD CONSTRAINT "field vytvoril user" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."import_tags" ADD CONSTRAINT "tagy na import" FOREIGN KEY ("id_import")
    REFERENCES "public"."import_settings"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."import_settings" ADD CONSTRAINT "vytvoril uzivatel" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."evaluation" ADD CONSTRAINT "patrim do skupiny" FOREIGN KEY ("id_parent")
    REFERENCES "public"."evaluation"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."evaluation" ADD CONSTRAINT "evaluation v stringu" FOREIGN KEY ("name")
    REFERENCES "public"."string_ids"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."campaigns" ADD CONSTRAINT "kampan v stringu" FOREIGN KEY ("name")
    REFERENCES "public"."string_ids"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."campaigns" ADD CONSTRAINT "kamapn vytvoril user" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."campaigns" ADD CONSTRAINT "kampan pouziva template_set" FOREIGN KEY ("id_template_set")
    REFERENCES "public"."template_sets"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."campaigns" ADD CONSTRAINT "email_template pre kampan" FOREIGN KEY ("id_email_template")
    REFERENCES "public"."email_templates"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."campaign_surveys" ADD CONSTRAINT "aky vyzor ma prva stranka?" FOREIGN KEY ("first_page")
    REFERENCES "public"."templates"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."campaign_surveys" ADD CONSTRAINT "dotaznik patri do kampane" FOREIGN KEY ("id_survey")
    REFERENCES "public"."surveys"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."campaign_surveys" ADD CONSTRAINT "kampan ma surveye" FOREIGN KEY ("id_campaign")
    REFERENCES "public"."campaigns"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."email_templates" ADD CONSTRAINT "nazov templatu v stringu" FOREIGN KEY ("name")
    REFERENCES "public"."string_ids"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."email_templates" ADD CONSTRAINT "otazku vytvoril user" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."choices" ADD CONSTRAINT "moznost patri otazke" FOREIGN KEY ("id_question")
    REFERENCES "public"."questions"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."choices" ADD CONSTRAINT "moznost v stringu" FOREIGN KEY ("text")
    REFERENCES "public"."string_ids"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."invitation_fields" ADD CONSTRAINT "dana hodnota" FOREIGN KEY ("id_field")
    REFERENCES "public"."fields"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."invitation_fields" ADD CONSTRAINT "fieldy v pozvanke" FOREIGN KEY ("id_invitation")
    REFERENCES "public"."invitations"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."object_tags" ADD CONSTRAINT "objekt ma tagy" FOREIGN KEY ("id_object")
    REFERENCES "public"."objects"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."object_properties" ADD CONSTRAINT "hodnota v stringu" FOREIGN KEY ("text_value")
    REFERENCES "public"."string_ids"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."object_properties" ADD CONSTRAINT "properties patriace objektu" FOREIGN KEY ("id_object")
    REFERENCES "public"."objects"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."object_properties" ADD CONSTRAINT "vlastne properties k objektu" FOREIGN KEY ("id_property")
    REFERENCES "public"."properties"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."page_questions" ADD CONSTRAINT "skupina otazok ma otazky" FOREIGN KEY ("id_question")
    REFERENCES "public"."questions"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."page_questions" ADD CONSTRAINT "skupina otazok patri na stranku" FOREIGN KEY ("id_page")
    REFERENCES "public"."survey_pages"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."page_questions" ADD CONSTRAINT "otazka ma hodnotenie" FOREIGN KEY ("id_evaluation")
    REFERENCES "public"."evaluation"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."objects" ADD CONSTRAINT "objekt v stringu" FOREIGN KEY ("name")
    REFERENCES "public"."string_ids"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."objects" ADD CONSTRAINT "objekt vytvoril user" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE SET NULL
      NOT DEFERRABLE;

ALTER TABLE "public"."objects" ADD CONSTRAINT "pochadza z databazy" FOREIGN KEY ("id_source")
    REFERENCES "public"."sources"("id")
      MATCH SIMPLE
      ON DELETE SET NULL
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."media" ADD CONSTRAINT "media patria userovi" FOREIGN KEY ("id_user")
    REFERENCES "public"."users"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."invitations" ADD CONSTRAINT "pozvanka v jazyku" FOREIGN KEY ("id_lang")
    REFERENCES "public"."langs"("id")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."invitations" ADD CONSTRAINT "pozvanka na kampan" FOREIGN KEY ("id_campaign")
    REFERENCES "public"."campaigns"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."invitation_objects" ADD CONSTRAINT "je cast pozvanky " FOREIGN KEY ("id_invitation")
    REFERENCES "public"."invitations"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."invitation_objects" ADD CONSTRAINT "pozvanka na kampan/survey" FOREIGN KEY ("id_campaignsurvey")
    REFERENCES "public"."campaign_surveys"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;

ALTER TABLE "public"."invitation_objects" ADD CONSTRAINT "pozvanka na objekt" FOREIGN KEY ("id_object")
    REFERENCES "public"."objects"("id")
      MATCH SIMPLE
      ON DELETE CASCADE
      ON UPDATE CASCADE
      NOT DEFERRABLE;


CREATE OR REPLACE VIEW "campaign_survey_view" AS
	SELECT campaign_surveys.id, campaign_surveys.id_campaign, campaign_surveys.id_survey, campaign_surveys.first_page, campaign_surveys.ordering FROM campaign_surveys;

COMMENT ON VIEW "campaign_survey_view" IS '';


CREATE OR REPLACE RULE "c_s_delete" AS
	ON DELETE TO "campaign_survey_view"
	DO ALSO
		(SELECT change_order_after_delete('campaign_surveys'::text, ARRAY['id_campaign'::text], ARRAY[old.id_campaign], old.ordering) AS zmen_poradie_delete; DELETE FROM campaign_surveys WHERE (campaign_surveys.id = old.id););

CREATE OR REPLACE RULE "c_s_insert" AS
	ON INSERT TO "campaign_survey_view"
	DO ALSO
		(SELECT change_order_after_insert('campaign_surveys'::text, ARRAY['id_campaign'::text], ARRAY[new.id_campaign], new.ordering) AS zmen_poradie_insert; INSERT INTO campaign_surveys (id, id_campaign, id_survey, first_page, ordering) VALUES (currval('campaign_surveys_id_seq'::regclass), new.id_campaign, new.id_survey, new.first_page, new.ordering););

CREATE OR REPLACE RULE "c_s_update" AS
	ON UPDATE TO "campaign_survey_view"
	DO ALSO
		(SELECT change_order_after_update('campaign_surveys'::text, ARRAY['id_campaign'::text], ARRAY[old.id_campaign], new.ordering, old.ordering) AS update_poradie; UPDATE campaign_surveys SET id_campaign = new.id_campaign, id_survey = new.id_survey, first_page = new.first_page, ordering = new.ordering WHERE (campaign_surveys.id = new.id););

CREATE OR REPLACE VIEW "choices_view" AS
	SELECT choices.id, choices.id_question, choices.ordering, choices.text, choices.value FROM choices;

COMMENT ON VIEW "choices_view" IS '';


CREATE OR REPLACE RULE "c_delete" AS
	ON DELETE TO "choices_view"
	DO ALSO
		(SELECT change_order_after_delete('choices'::text, ARRAY['id_question'::text], ARRAY[old.id_question], old.ordering) AS zmen_poradie_delete; DELETE FROM choices WHERE (choices.id = old.id););

CREATE OR REPLACE RULE "c_insert" AS
	ON INSERT TO "choices_view"
	DO ALSO
		(SELECT change_order_after_insert('choices'::text, ARRAY['id_question'::text], ARRAY[new.id_question], new.ordering) AS zmen_poradie_insert; INSERT INTO choices (id, id_question, text, value, ordering) VALUES (currval('choices_id_seq'::regclass), new.id_question, new.text, new.value, new.ordering););

CREATE OR REPLACE RULE "c_update" AS
	ON UPDATE TO "choices_view"
	DO ALSO
		(SELECT change_order_after_update('choices'::text, ARRAY['id_question'::text], ARRAY[old.id_question], new.ordering, old.ordering) AS update_poradie; UPDATE choices SET id_question = new.id_question, ordering = new.ordering, text = new.text, value = new.value WHERE (choices.id = new.id););

CREATE OR REPLACE VIEW "page_questions_view" AS
	SELECT page_questions.id, page_questions.id_page, page_questions.id_question, page_questions.ordering, page_questions.description, page_questions.required, page_questions.weight FROM page_questions;

COMMENT ON VIEW "page_questions_view" IS '';


CREATE OR REPLACE RULE "p_g_delete" AS
	ON DELETE TO "page_questions_view"
	DO ALSO
		(SELECT change_order_after_delete('page_questions'::text, ARRAY['id_page'::text], ARRAY[old.id_page], old.ordering) AS zmen_poradie_delete; DELETE FROM page_questions WHERE (page_questions.id = old.id););

CREATE OR REPLACE RULE "p_g_insert" AS
	ON INSERT TO "page_questions_view"
	DO ALSO
		(SELECT change_order_after_insert('page_questions'::text, ARRAY['id_page'::text], ARRAY[new.id_page], new.ordering) AS zmen_poradie_insert; INSERT INTO page_questions (id, id_page, id_question, ordering, description, required, weight) VALUES (currval('page_questions_id_seq'::regclass), new.id_page, new.id_question, new.ordering, new.description, new.required, new.weight););

CREATE OR REPLACE RULE "p_g_update" AS
	ON UPDATE TO "page_questions_view"
	DO ALSO
		(SELECT change_order_after_update('page_questions'::text, ARRAY['id_page'::text], ARRAY[old.id_page], new.ordering, old.ordering) AS update_poradie; UPDATE page_questions SET id_page = new.id_page, id_question = new.id_question, description = new.description, required = new.required, weight = new.weight, ordering = new.ordering WHERE (page_questions.id = new.id););

CREATE OR REPLACE VIEW "responses_view" AS
	SELECT ru.id AS run,
sr.id_campaign_survey,
sr.id_object,
ru.id_invitation,
ru."timestamp",
re.id_page_question,
re.int_value,
re.text_value
FROM
survey_runs sr,
runs ru,
responses re
WHERE
(
     (ru.id = sr.id_run)
     AND
     (re.id_survey_run = sr.id)
);

COMMENT ON VIEW "responses_view" IS '';


CREATE OR REPLACE RULE "resp_delete" AS
	ON DELETE TO "responses_view"
	DO ALSO NOTHING;

CREATE OR REPLACE RULE "resp_insert" AS
	ON INSERT TO "responses_view"
	DO ALSO NOTHING;

CREATE OR REPLACE RULE "resp_update" AS
	ON UPDATE TO "responses_view"
	DO ALSO NOTHING;

CREATE OR REPLACE VIEW "survey_pages_view" AS
	SELECT survey_pages.id, survey_pages.id_survey, survey_pages.title, survey_pages.ordering FROM survey_pages;

COMMENT ON VIEW "survey_pages_view" IS '';


CREATE OR REPLACE RULE "s_p_delete" AS
	ON DELETE TO "survey_pages_view"
	DO ALSO
		(SELECT change_order_after_delete('survey_pages'::text, ARRAY['id_survey'::text], ARRAY[old.id_survey], old.ordering) AS zmen_poradie_delete; DELETE FROM survey_pages WHERE (survey_pages.id = old.id););

CREATE OR REPLACE RULE "s_p_insert" AS
	ON INSERT TO "survey_pages_view"
	DO ALSO
		(SELECT change_order_after_insert('survey_pages'::text, ARRAY['id_survey'::text], ARRAY[new.id_survey], new.ordering) AS zmen_poradie_insert; INSERT INTO survey_pages (id, id_survey, title, ordering) VALUES (currval('survey_pages_id_seq'::regclass), new.id_survey, new.title, new.ordering););

CREATE OR REPLACE RULE "s_p_update" AS
	ON UPDATE TO "survey_pages_view"
	DO ALSO
		(SELECT change_order_after_update('survey_pages'::text, ARRAY['id_survey'::text], ARRAY[old.id_survey], new.ordering, old.ordering) AS update_poradie; UPDATE survey_pages SET id_survey = new.id_survey, title = new.title, ordering = new.ordering WHERE (survey_pages.id = new.id););





CREATE OR REPLACE VIEW "avg_pq_o_cs_y" AS
	SELECT
  s_r.id_object AS "id_object",
  id_campaign_survey AS "id_campaign_survey",
  id_page_question AS "id_page_question",
  AVG(resp.int_value) AS "avg",
  TO_CHAR(r.timestamp,'YYYY') AS "date"
FROM
  responses resp,
  survey_runs s_r,
  runs r
WHERE
  r.id = s_r.id_run
 AND
  resp.id_survey_run = s_r.id
GROUP BY date, id_object, id_page_question, id_campaign_survey
ORDER BY date;

COMMENT ON VIEW "avg_pq_o_cs_y" IS 'priemer:
- odpoved na danu otazku (page_question)
- pre dany objekt
- pre dany dotaznik/kampan (campaign_survey)
- groupovane podla rokov, objektom, otazkou, campaign_survey';


CREATE OR REPLACE RULE "upd" AS
	ON UPDATE TO "avg_pq_o_cs_y"
	DO INSTEAD
		();

CREATE OR REPLACE RULE "ins" AS
	ON INSERT TO "avg_pq_o_cs_y"
	DO INSTEAD
		();

CREATE OR REPLACE RULE "del" AS
	ON DELETE TO "avg_pq_o_cs_y"
	DO INSTEAD
		();





CREATE OR REPLACE VIEW "avg_pq_o_cs_ym" AS
	SELECT
  s_r.id_object AS "id_object",
  id_page_question AS "id_page_question",
  id_campaign_survey AS "id_campaign_survey",
  AVG(resp.int_value) AS "avg",
  TO_CHAR(r.timestamp,'YYYY') || '_' ||
  TO_CHAR(r.timestamp,'MM') AS "date"
FROM
  responses resp,
  survey_runs s_r,
  runs r
WHERE
  r.id = s_r.id_run
 AND
  resp.id_survey_run = s_r.id
GROUP BY date, id_object, id_page_question, id_campaign_survey
ORDER BY date;

COMMENT ON VIEW "avg_pq_o_cs_ym" IS 'priemer:
- odpoved na danu otazku (page_question)
- pre dany objekt
- pre dany dotaznik/kampan (campaign_survey)
- groupovane podla rokov, mesiacov, objektom, otazkou, campaign_survey
';


CREATE OR REPLACE RULE "upd" AS
	ON UPDATE TO "avg_pq_o_cs_ym"
	DO INSTEAD
		();

CREATE OR REPLACE RULE "ins" AS
	ON INSERT TO "avg_pq_o_cs_ym"
	DO INSTEAD
		();

CREATE OR REPLACE RULE "del" AS
	ON DELETE TO "avg_pq_o_cs_ym"
	DO INSTEAD
		();





CREATE OR REPLACE VIEW "avg_pq_o_cs_ymd" AS
	SELECT
  s_r.id_object AS "id_object",
  id_page_question AS "id_page_question",
  id_campaign_survey as "id_campaign_survey",
  AVG(resp.int_value) AS "avg",
  TO_CHAR(r.timestamp, 'YYYY') || '_' ||
  TO_CHAR(r.timestamp, 'MM') || '_' ||
  TO_CHAR(r.timestamp, 'DD') AS "date"
FROM
  responses resp,
  survey_runs s_r,
  runs r
WHERE
  r.id = s_r.id_run
 AND
  resp.id_survey_run = s_r.id
GROUP BY date, id_object, id_page_question, id_campaign_survey
ORDER BY date;

COMMENT ON VIEW "avg_pq_o_cs_ymd" IS 'priemer:
- odpoved na danu otazku (page_question)
- pre dany objekt
- pre dany dotaznik/kampan (campaign_survey)
- groupovane podla rokov, mesiacov, dni, objektom, otazkou, campaign_survey';


CREATE OR REPLACE RULE "upd" AS
	ON UPDATE TO "avg_pq_o_cs_ymd"
	DO INSTEAD
		();

CREATE OR REPLACE RULE "ins" AS
	ON INSERT TO "avg_pq_o_cs_ymd"
	DO INSTEAD
		();

CREATE OR REPLACE RULE "del" AS
	ON DELETE TO "avg_pq_o_cs_ymd"
	DO INSTEAD
		();

CREATE OR REPLACE VIEW "avg_pq_o_cs_yw" AS
	SELECT
  s_r.id_object AS "id_object",
  id_page_question AS "id_page_question",
  id_campaign_survey AS "id_campaign_survey",
  AVG(resp.int_value) AS "avg",
  TO_CHAR(r.timestamp,'YYYY') || '_' ||
  TO_CHAR(r.timestamp,'WW') AS "date"
FROM
  responses resp,
  survey_runs s_r,
  runs r
WHERE
  r.id = s_r.id_run
 AND
  resp.id_survey_run = s_r.id
GROUP BY date, id_object, id_page_question, id_campaign_survey
ORDER BY date;

COMMENT ON VIEW "avg_pq_o_cs_yw" IS 'priemer:
- odpoved na danu otazku (page_question)
- pre dany objekt
- pre dany dotaznik/kampan (campaign_survey)
- groupovane podla rokov, tyzdnov, objektom, otazkou, campaign_survey';


CREATE OR REPLACE RULE "upd" AS
	ON UPDATE TO "avg_pq_o_cs_yw"
	DO INSTEAD
		();

CREATE OR REPLACE RULE "ins" AS
	ON INSERT TO "avg_pq_o_cs_yw"
	DO INSTEAD
		();

CREATE OR REPLACE RULE "del" AS
	ON DELETE TO "avg_pq_o_cs_yw"
	DO INSTEAD
		();

CREATE OR REPLACE VIEW "responses_translated_view" AS
	SELECT
ru.id AS run,
sr.id_campaign_survey,
TRANSLATE(c.name, l.id) AS "campaign",
TRANSLATE(s.name, l.id) AS "survey",
sr.id_object,
TRANSLATE(o.name, l.id) AS "object",
ru.id_invitation,
ru."timestamp",
re.id_page_question,
TRANSLATE(sp.title, l.id) AS "page",
TRANSLATE(q.text, l.id) AS "question",
re.int_value,
re.text_value,
l.id AS "id_lang"
FROM
survey_runs sr,
runs ru,
responses re,
campaigns c,
surveys s,
objects o,
campaign_surveys cs,
page_questions pq,
questions q,
survey_pages sp,
langs l
WHERE
(
     (ru.id = sr.id_run)
     AND
     (re.id_survey_run = sr.id)
     AND
     (c.id =  cs.id_campaign)
     AND
     (s.id = cs.id_survey)
     AND
     (cs.id = sr.id_campaign_survey)
     AND
     (o.id = sr.id_object)
     AND
     (pq.id = re.id_page_question)
     AND
     (q.id = pq.id_question)
     AND
     (sp.id = pq.id_page)
);

COMMENT ON VIEW "responses_translated_view" IS '';


CREATE OR REPLACE RULE "resp_delete" AS
	ON DELETE TO "responses_translated_view"
	DO ALSO NOTHING;

CREATE OR REPLACE RULE "resp_insert" AS
	ON INSERT TO "responses_translated_view"
	DO ALSO NOTHING;

CREATE OR REPLACE RULE "resp_update" AS
	ON UPDATE TO "responses_translated_view"
	DO ALSO NOTHING;

